package com.fckbug.dao.impl;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.fckbug.dao.QADAO;

public class QADAOImpl extends HibernateDaoSupport implements QADAO {
	private static final String MAX_Q_ID = "SELECT MAX(ID)+1 FROM QUESTION";
	private static final String SAVE_Q = "INSERT INTO QUESTION(TITLE,CONTENT,USERID,CTTIME) VALUES(:TITLE,:CONTENT,:USERID,NOW())";
	private static final String SAVE_Q_T = "INSERT INTO QTIME(QID) VALUES(:QID)";
	private static final String SAVE_A = "INSERT INTO ANSWER(QID,REPLY,USERID,CTTIME) VALUES(:QID,:REPLY,:USERID,NOW())";
	private static final String SAVE_A_T = "INSERT INTO ATIME(AID) VALUES(:AID)";
	private static final String SAVE_C = "INSERT INTO COMMENT(TYPE,QAID,CONTENT,USERID,CTTIME) VALUES(:TYPE,:QAID,:CONTENT,:USERID,NOW())";
	private static final String ALTER_Q_T = "UPDATE QTIME SET MARK=MARK+:INCREMENT WHERE QID=:QID";
	private static final String ALTER_F_T = "UPDATE QTIME SET FAVORITE=FAVORITE+:INCREMENT WHERE QID=:QID";
	private static final String ALTER_V_T = "UPDATE QTIME SET VIEW=VIEW+1 WHERE QID=:QID";
	private static final String ALTER_A_T = "UPDATE ATIME SET MARK=MARK+:INCREMENT WHERE AID=:AID";
	private static final String SAVE_Q_USER = "INSERT INTO USERQMARK SELECT :USERID,:QID FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM USERQMARK WHERE USERID=:USERID AND QID=:QID)";
	private static final String SAVE_F_USER = "INSERT INTO USERFMARK SELECT :USERID,:QID FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM USERFMARK WHERE USERID=:USERID AND QID=:QID)";
	private static final String SAVE_A_USER = "INSERT INTO USERAMARK SELECT :USERID,:AID FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM USERAMARK WHERE USERID=:USERID AND AID=:AID)";
	private static final String QUERY_Q = "SELECT TITLE,CONTENT,CTTIME,USERID FROM QUESTION WHERE ID=:ID";
	private static final String QUERY_A = "SELECT ID,REPLY,USERID FROM ANSWER WHERE QID=:QID";
	private static final String QUERY_Q_TIME = "SELECT MARK,FAVORITE,VIEW FROM QTIME WHERE QID=:QID";
	private static final String QUERY_A_TIME = "SELECT MARK FROM ATIME WHERE AID=:AID";
	private static final String QUERY_COMMENT = "SELECT CONTENT,USERID,CTTIME FROM COMMENT WHERE TYPE=:TYPE AND QAID=:QAID ORDER BY CTTIME DESC";
	private static final String QUERY_UNANSWER = "SELECT Q.ID,Q.TITLE,Q.USERID,Q.CTTIME,QT.MARK,QT.VIEW,0 FROM QUESTION Q,QTIME QT WHERE NOT EXISTS (SELECT 1 FROM ANSWER WHERE QID=Q.ID) AND Q.ID=QT.QID ORDER BY Q.CTTIME DESC LIMIT :start,:offset";
	private static final String QUERY_ALL_Q = "SELECT Q.ID,Q.TITLE,Q.USERID,Q.CTTIME,QT.MARK,QT.VIEW,(SELECT COUNT(1) FROM ANSWER WHERE QID=Q.ID) FROM QUESTION Q,QTIME QT WHERE Q.ID=QT.QID ORDER BY QT.VIEW DESC LIMIT :start,:offset";
	
	private static final String SELECT_IDENTITY = "SELECT @@IDENTITY";

	@Override
	public Integer saveQ(final String title, final String content, final int userid) {
		return (Integer)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(SAVE_Q);
					query.setString("TITLE", title);
					query.setString("CONTENT", content);
					query.setInteger("USERID", userid);
					if(1 == query.executeUpdate()) {
						query = session.createSQLQuery(SELECT_IDENTITY);
						BigInteger bi = (BigInteger)query.uniqueResult();
						return bi.intValue();
					} else {
						return null;
					}
				}
			}
		);
	}
	
	@Override
	public Integer saveQT(final Integer qId) {
		return (Integer)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Integer doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(SAVE_Q_T);
					query.setInteger("QID", qId);
					return query.executeUpdate();
				}
			}
		);
	}


	@Override
	public int saveTag(int qid) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public List getQ(final Integer id) {
		return (List)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(QUERY_Q);
					query.setLong("ID", id.longValue());
					List list = query.list();
					return list;
				}
			}
		);
	}

	@Override
	public List getA(final Integer qId) {
		return (List)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(QUERY_A);
					query.setInteger("QID", qId);
					List list = query.list();
					return list;
				}
			}
		);
	}

	@Override
	public Integer saveA(final long qId, final String content, final int userid) {
		return (Integer)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(SAVE_A);
					query.setLong("QID", qId);
					query.setString("REPLY", content);
					query.setInteger("USERID", userid);
					if(1==query.executeUpdate()) {
						query = session.createSQLQuery(SELECT_IDENTITY);
						BigInteger bi = (BigInteger)query.uniqueResult();
						return bi.intValue();
					}
					return -1;
				}
			}
		);
	}

	public List getQTime(final Integer qId) {
		return (List)this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session) throws HibernateException, SQLException {
						SQLQuery query = session.createSQLQuery(QUERY_Q_TIME);
						query.setInteger("QID", qId);
						List list = query.list();
						return list;
					}
				}
			);
	}
	
	public List getATime(final Integer aId) {
		return (List)this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session) throws HibernateException, SQLException {
						SQLQuery query = session.createSQLQuery(QUERY_A_TIME);
						query.setInteger("AID", aId);
						List list = query.list();
						return list;
					}
				}
			);
	}

	@Override
	public Integer saveAT(final Integer aId) {
		return (Integer)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Integer doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(SAVE_A_T);
					query.setInteger("AID", aId);
					return query.executeUpdate();
				}
			}
		);
	}
	
	@Override
	public Integer alterQTime(final int qId, final int increment) {
		return (Integer)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Integer doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(ALTER_Q_T);
					query.setInteger("QID", qId);
					query.setInteger("INCREMENT", increment);
					return query.executeUpdate();
				}
			}
		);
	}
	
	@Override
	public Integer alterQVTime(final int qId) {
		return (Integer)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Integer doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(ALTER_V_T);
					query.setInteger("QID", qId);
					return query.executeUpdate();
				}
			}
		);
	}
	
	@Override
	public Integer alterFTime(final int qId, final int increment) {
		return (Integer)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Integer doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(ALTER_F_T);
					query.setInteger("QID", qId);
					query.setInteger("INCREMENT", increment);
					return query.executeUpdate();
				}
			}
		);
	}

	@Override
	public Integer alterATime(final int aId, final int increment) {
		return (Integer)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Integer doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(ALTER_A_T);
					query.setInteger("AID", aId);
					query.setInteger("INCREMENT", increment);
					return query.executeUpdate();
				}
			}
		);
	}

	@Override
	public Integer saveUserAMark(final int userId, final int aId) throws Exception {
		return (Integer)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Integer doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(SAVE_A_USER);
					query.setInteger("USERID", userId);
					query.setInteger("AID", aId);
					return query.executeUpdate();
				}
			}
		);
	}

	@Override
	public Integer saveUserQMark(final int userId, final int qId) throws Exception {
		return (Integer)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Integer doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(SAVE_Q_USER);
					query.setInteger("USERID", userId);
					query.setInteger("QID", qId);
					return query.executeUpdate();
				}
			}
		);
	}

	@Override
	public Integer saveUserFMark(final int userId, final int qId) throws Exception {
		return (Integer)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Integer doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(SAVE_F_USER);
					query.setInteger("USERID", userId);
					query.setInteger("QID", qId);
					return query.executeUpdate();
				}
			}
		);
	}

	@Override
	public List getComment(final int type, final int qaId) {
		return (List)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(QUERY_COMMENT);
					query.setInteger("TYPE", type);
					query.setInteger("QAID", qaId);
					List list = query.list();
					return list;
				}
			}
		);
	}

	@Override
	public Integer saveC(final int qaId, final int type, final String content, final int userid) {
		return (Integer)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(SAVE_C);
					query.setInteger("TYPE", type);
					query.setInteger("QAID", qaId);
					query.setString("CONTENT", content);
					query.setInteger("USERID", userid);
					return query.executeUpdate();
				}
			}
		);
	}

	@Override
	public List getUnAnsweredQ(final int start, final int offset) {
		return (List)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(QUERY_UNANSWER);
					query.setInteger("start", start);
					query.setInteger("offset", offset);
					List list = query.list();
					return list;
				}
			}
		);
	}

	@Override
	public List getAllQ(final int start, final int offset) {
		return (List)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(QUERY_ALL_Q);
					query.setInteger("start", start);
					query.setInteger("offset", offset);
					List list = query.list();
					return list;
				}
			}
		);
	}


}
